﻿using System;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Data.SqlClient;

using SIS.ENT;

namespace SIS.DAL
{
    public class EmployeeTimeSheetTableDB
    {
        #region "Public Methods"

        /// <summary>
        /// Gets an instance of EmployeeAttandanceTable from the underlying datasource.
        /// </summary>
        /// <param name="employeeTimeSheetKey">The unique EmployeeAttandanceKey of the EmployeeAttandanceTable in the database.</param>
        /// <returns>An EmployeeAttandanceTable when the EmployeeAttandanceKey was found in the database, or null otherwise.</returns>
        public static EmployeeTimeSheetTable GetItem(long employeeTimeSheetKey)
        {
            EmployeeTimeSheetTable myEmployeeAttandanceTable = null;
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocEmployeeTimeSheetTableSelectSingleItem", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@employeeTimeSheetKey", employeeTimeSheetKey);

                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.Read())
                        {
                            myEmployeeAttandanceTable = FillDataRecord(myReader);
                        }
                        myReader.Close();
                    }
                    myConnection.Close();
                }
                return myEmployeeAttandanceTable;
            }
        }

        /// <summary>
        /// Returns a list with EmployeeAttandanceTable objects.
        /// </summary>
        /// <returns>A generics List with the EmployeeAttandanceTable objects.</returns>
        /// 
        public static EmployeeTimeSheetTableList GetList()
        {
            EmployeeTimeSheetTableList tempList = new EmployeeTimeSheetTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocEmployeeTimeSheetTableSelectListByEmployeeProfileKey", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;

                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
            }
            return tempList;
        }

        /// <summary>
        /// Returns a list with EmployeeAttandanceTable objects.
        /// </summary>
        /// <returns>A generics List with the EmployeeAttandanceTable objects.</returns>
        public static EmployeeTimeSheetTableList GetListByEmployeeProfileKey(int EmployeeProfileKey)
        {
            EmployeeTimeSheetTableList tempList = new EmployeeTimeSheetTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocEmployeeTimeSheetTableSelectListByEmployeeProfileKey", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@employeeProfileKey", EmployeeProfileKey);
                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
                return tempList;
            }
        }

        public static EmployeeTimeSheetTableList GetListBySchoolNUserAccount(int SchoolKey, int UserAccountKey, DateTime StartDate, DateTime EndDate)
        {
            EmployeeTimeSheetTableList tempList = new EmployeeTimeSheetTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocEmployeeTimeSheetTableSelectListBySchoolKeyNUserAccount", myConnection))
                {

                    myCommand.CommandType = CommandType.StoredProcedure;

                    myCommand.Parameters.AddWithValue("@SchoolKey", SchoolKey);
                    myCommand.Parameters.AddWithValue("@UserAccountKey", UserAccountKey);
                    myCommand.Parameters.AddWithValue("@StartDate", StartDate);
                    myCommand.Parameters.AddWithValue("@EndDate", EndDate);

                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
            }
            return tempList;
        }

        public static EmployeeTimeSheetTableList GetList(int SchoolKey, string EmployeeProfileKey, DateTime StartDate, DateTime EndDate)
        {
            string Start = StartDate.ToString("yyyy-MM-dd");
            string End = EndDate.ToString("yyyy-MM-dd");

            EmployeeTimeSheetTableList  tempList = new EmployeeTimeSheetTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocEmployeeTimeSheetTableSelectListBySchoolKeyNEmployee", myConnection))
                {

                    myCommand.CommandType = CommandType.StoredProcedure;

                    myCommand.Parameters.AddWithValue("@SchoolKey", SchoolKey);
                    myCommand.Parameters.AddWithValue("@EmployeeProfileKey", EmployeeProfileKey);
                    myCommand.Parameters.AddWithValue("@StartDate", Start);
                    myCommand.Parameters.AddWithValue("@EndDate", End);

                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
            }
            return tempList;
        }
        /// <summary>
        /// Returns a list with EmployeeAttandanceTable objects.
        /// </summary>
        /// <returns>A generics List with the EmployeeAttandanceTable objects.</returns>
        public static EmployeeTimeSheetTableList GetListByUserAccountKey(int UserAccountKey)
        {
            EmployeeTimeSheetTableList tempList = new EmployeeTimeSheetTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocEmployeeTimeSheetTableSelectListByUserAccountKey", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@userAccountKey", UserAccountKey);
                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
                return tempList;
            }
        }
        /// <summary>
        /// Saves a EmployeeAttandanceTable in the database.
        /// </summary>
        /// <param name="myEmployeeTimeSheetTable">The EmployeeAttandanceTable instance to save.</param>
        /// <returns>The new EmployeeAttandanceKey if the EmployeeAttandanceTable is new in the database or the existing EmployeeAttandanceKey when an item was updated.</returns>
        public static int Save(EmployeeTimeSheetTable myEmployeeTimeSheetTable)
        {
            int result = 0;
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocEmployeeTimeSheetTableInsertUpdateSingleItem", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;

                    if (myEmployeeTimeSheetTable.EmployeeTimeSheetKey == -1)
                    {
                        myCommand.Parameters.AddWithValue("@employeeTimeSheetKey", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@employeeTimeSheetKey", myEmployeeTimeSheetTable.EmployeeTimeSheetKey);
                    }
                    if (myEmployeeTimeSheetTable.EmployeeProfileKey == null)
                    {
                        myCommand.Parameters.AddWithValue("@employeeProfileKey", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@employeeProfileKey", myEmployeeTimeSheetTable.EmployeeProfileKey);
                    }
                    if (myEmployeeTimeSheetTable.UserAccountKey == null)
                    {
                        myCommand.Parameters.AddWithValue("@userAccountKey", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@userAccountKey", myEmployeeTimeSheetTable.UserAccountKey);
                    }
                    if (myEmployeeTimeSheetTable.DayCode == null)
                    {
                        myCommand.Parameters.AddWithValue("@dayCode", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@dayCode", myEmployeeTimeSheetTable.DayCode);
                    }
                    if (myEmployeeTimeSheetTable.TimeIn == null)
                    {
                        myCommand.Parameters.AddWithValue("@timeIn", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@timeIn", myEmployeeTimeSheetTable.TimeIn);
                    }
                    if (myEmployeeTimeSheetTable.TimeOut == null)
                    {
                        myCommand.Parameters.AddWithValue("@timeOut", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@timeOut", myEmployeeTimeSheetTable.TimeOut);
                    }
                    if (string.IsNullOrEmpty(myEmployeeTimeSheetTable.Remarks))
                    {
                        myCommand.Parameters.AddWithValue("@remarks", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@remarks", myEmployeeTimeSheetTable.Remarks);
                    }
                    if (myEmployeeTimeSheetTable.TimeSheetStatus == null)
                    {
                        myCommand.Parameters.AddWithValue("@TimeSheetStatus", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@TimeSheetStatus", myEmployeeTimeSheetTable.TimeSheetStatus);
                    }
                    if (myEmployeeTimeSheetTable.Status == null)
                    {
                        myCommand.Parameters.AddWithValue("@status", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@status", myEmployeeTimeSheetTable.Status);
                    }
                    if (myEmployeeTimeSheetTable.CreatedDateTime == null)
                    {
                        myCommand.Parameters.AddWithValue("@createdDateTime", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@createdDateTime", myEmployeeTimeSheetTable.CreatedDateTime);
                    }
                    if (myEmployeeTimeSheetTable.ModifiedDateTime == null)
                    {
                        myCommand.Parameters.AddWithValue("@modifiedDateTime", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@modifiedDateTime", myEmployeeTimeSheetTable.ModifiedDateTime);
                    }

                    DbParameter returnValue;
                    returnValue = myCommand.CreateParameter();
                    returnValue.Direction = ParameterDirection.ReturnValue;
                    myCommand.Parameters.Add(returnValue);

                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
                    result = Convert.ToInt32(returnValue.Value);
                    myConnection.Close();
                }
            }
            return result;
        }
        /// <summary>
        /// Deletes a EmployeeAttandanceTable from the database.
        /// </summary>
        /// <param name="EmployeeTimeSheetKey">The EmployeeAttandanceKey of the EmployeeAttandanceTable to delete.</param>
        /// <returns>Returns true when the object was deleted successfully, or false otherwise.</returns>
        public static bool Delete(long EmployeeTimeSheetKey)
        {
            int result = 0;
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocEmployeeTimeSheetTableDeleteSingleItem", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;

                    myCommand.Parameters.AddWithValue("@employeeTimeSheetKey", EmployeeTimeSheetKey);
                    myConnection.Open();
                    result = myCommand.ExecuteNonQuery();
                    myConnection.Close();
                }
            }
            return result > 0;
        }

        #endregion

        /// <summary>
        /// Initializes a new instance of the EmployeeAttandanceTable class and fills it with the data fom the IDataRecord.
        /// </summary>
        /// 

        private static EmployeeTimeSheetTable FillDataRecord(IDataRecord myDataRecord)
        {
            EmployeeTimeSheetTable myEmployeeAttandanceTable = new EmployeeTimeSheetTable();
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("EmployeeTimeSheetKey")))
            {
                myEmployeeAttandanceTable.EmployeeTimeSheetKey = myDataRecord.GetInt64(myDataRecord.GetOrdinal("EmployeeTimeSheetKey"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("EmployeeProfileKey")))
            {
                myEmployeeAttandanceTable.EmployeeProfileKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("EmployeeProfileKey"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("UserAccountKey")))
            {
                myEmployeeAttandanceTable.UserAccountKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("UserAccountKey"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("TimeIn")))
            {
                myEmployeeAttandanceTable.TimeIn = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("TimeIn"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("TimeOut")))
            {
                myEmployeeAttandanceTable.TimeOut = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("TimeOut"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Remarks")))
            {
                myEmployeeAttandanceTable.Remarks = myDataRecord.GetString(myDataRecord.GetOrdinal("Remarks"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("TimeSheetStatus")))
            {
                myEmployeeAttandanceTable.TimeSheetStatus = myDataRecord.GetBoolean(myDataRecord.GetOrdinal("TimeSheetStatus"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Status")))
            {
                myEmployeeAttandanceTable.Status = myDataRecord.GetInt16(myDataRecord.GetOrdinal("Status"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("CreatedDateTime")))
            {
                myEmployeeAttandanceTable.CreatedDateTime = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("CreatedDateTime"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("ModifiedDateTime")))
            {
                myEmployeeAttandanceTable.ModifiedDateTime = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("ModifiedDateTime"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("EmployeeName")))
            {
                myEmployeeAttandanceTable.Employee = myDataRecord.GetString(myDataRecord.GetOrdinal("EmployeeName"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("SchoolKey")))
            {
                myEmployeeAttandanceTable.SchoolKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("SchoolKey"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("DayCode")))
            {
                myEmployeeAttandanceTable.DayCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("DayCode"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("DayCodeDesc")))
            {
                myEmployeeAttandanceTable.DayCodeDesc = myDataRecord.GetString(myDataRecord.GetOrdinal("DayCodeDesc"));
            }
            return myEmployeeAttandanceTable;
        }

        public static bool UpdateTimeOut(long EmployeeTimeSheetKey, DateTime TimeOut)
        {
            int result = 0;
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocEmployeeAttandanceTableUpdateTimeOut", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;

                    myCommand.Parameters.AddWithValue("@employeeTimeSheetKey", EmployeeTimeSheetKey);
                    myCommand.Parameters.AddWithValue("@TimeOut", TimeOut);
                    myConnection.Open();
                    result = myCommand.ExecuteNonQuery();
                    myConnection.Close();
                }
            }
            return result > 0;
        }

       
    }
    
}
